﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data.SqlClient;
using System.Data;
using System.Collections;

namespace NextViewer
{
    class DBMS
    {
        private DBMS()
        {
            data = new Object[1024];
        }
        public static DBMS GetDBMS()
        {
            if (mDBMS == null)
                mDBMS = new DBMS();
            return mDBMS;
        }

        public void Request(string str, string[] param)
        {
            if (str == "COMPNET")
            {
                sql = @"SELECT t1.TIME, t1.LOC_LAT, t1.LOC_LON, t1.LOC_ACCURACY, t1.LOC_SPEED , t1.ID, t2.LOC_LAT, t2.LOC_LON, t2.LOC_ACCURACY, t2.LOC_SPEED , t2.ID            
                    FROM 	
	                    (SELECT TIME, GPS, LOC_LAT, LOC_LON, LOC_SPEED, LOC_ACCURACY, ID            
	                    FROM db_owner.UserInfo 
	                    WHERE 
		                     GPS = 1) t1
	                    inner join
	                    (SELECT TIME, GPS, LOC_LAT, LOC_LON, LOC_SPEED, LOC_ACCURACY, ID            
	                    FROM db_owner.UserInfo 
	                    WHERE 
		                     GPS != 1 and LOC_LAT !=0 and LOC_LON !=0
	                    ) t2
	                    on t1.time = t2.time and t1.ID != t2.ID	

                    ORDER BY t1.ID, TIME;";

            }
            else if (str == "GPS_PAIR")
            {
                sql = @"SELECT LOC_LAT, LOC_LON, dValue1, dValue2, TIME
                        FROM db_owner.UserInfo
                        WHERE dValue1 is not null and 
                 ID='" + param[0] + @"' and 
                 time >= CONVERT(datetime,'" + param[1] + @"') and 
                 time <= CONVERT(datetime,'" + param[2] + @"') ;";

            }
            else if (str == "GET_SPOTS")
            {
                sql = @"SELECT TIME,WIFI, GPS, LOC_LAT, LOC_LON, LOC_ACCURACY, LOC_SPEED
                 FROM db_owner.UserInfo WHERE ID='" + param[0] + @"'
                 and CONVERT(date,time) = CONVERT(date,'" + param[1] + @"')
                 ORDER BY time";
            }
            else if (str == "GET_SPOTS_PE")
            {
                sql = @"SELECT TIME,WIFI, GPS, LOC_LAT, LOC_LON, LOC_ACCURACY, LOC_SPEED
                 FROM db_owner.UserInfo WHERE ID='" + param[0] + @"' and 
                 time >= CONVERT(datetime,'" + param[1] + @"') and 
                 time <= CONVERT(datetime,'" + param[2] + @"') 
                 ORDER BY time";
            }
            else if (str == "COMPGPS")
            {
                sql = @"SELECT LOC_LAT, LOC_LON FROM db_owner.UserInfo WHERE 
                    GPS = 1 and LOC_LAT !=0 and LOC_LON !=0  and time = CONVERT(datetime,'" + param[0] + "')";

            }
            else if (str == "COMPFAKE")
            {
                sql = @"Select t1.LOC_LAT, t1.LOC_LON, t2.LOC_LAT, t2.LOC_LON, t1.time
                        FROM 
                        (Select time, ID, LOC_LAT, LOC_LON
                        FROM db_owner.UserInfo
                        WHERE GPS = 7
                        )t1
                        inner join
                        (select *
                        FROM db_owner.UserInfo
                        where strValue1 is not null
                        )t2
                        on t1.ID = t2.ID and t1.TIME = t2.TIME
                        order by t1.TIME";

            }
            else if (str == "DISTGPS")
            {
                sql = @"Select LOC_LAT, LOC_LON, dValue1, dValue2
                        from Next.db_owner.UserInfo
                        WHERE LOC_LAT is not null and dValue1 is not null and LOC_LAT != 0";
            }
        }
        public void Connect()
        {
            sqlConn = new SqlConnection("Data Source=147.46.240.145;Initial Catalog=Next;User ID = Next;Password=GAopt3132;Timeout=10000");
            SqlCommand ct = sqlConn.CreateCommand(); //DDL
            ct.CommandTimeout = 100000;
            SqlDataAdapter sqla = new SqlDataAdapter(sql, sqlConn);


            DataSet ds = new DataSet();
            DataTable dt_gap = new DataTable();
            try
            {
                sqlConn.Open();
                ct.CommandText = sql;
                sqla.SelectCommand = ct;
                sqla.Fill(ds);

                dt_gap = ds.Tables[0];

                drCol = dt_gap.Rows;
                dataIndex = 0;
            }
            catch (SqlException ee)
            {
                Console.WriteLine(sql);
                Console.WriteLine(ee.ToString());
                sqlConn.Close();
                return;
            }           
        }
        public bool Next()
        {
            DataRow dr;
            int i;
            if (drCol == null)
                return false;
         
            if (dataIndex >= drCol.Count)
                return false;
            

            dr = drCol[dataIndex];
            dataIndex++;
            i = 0;


            foreach (object item in dr.ItemArray)
            {
                data[i++] = item;
            }
            return true;
        }
        public void Close()
        {
            if (this.sqlConn != null)
                sqlConn.Close();
            sqlConn = null;
            return;
        }
        public String[] GetResult(string query)
        {
            SqlConnection sqlConn = new SqlConnection("Data Source=147.46.240.145;Initial Catalog=Next;User ID = Next;Password=GAopt3132;Timeout=10000");
            SqlCommand ct = sqlConn.CreateCommand(); //DDL
            ct.CommandTimeout = 100000;
            SqlDataAdapter sqla = new SqlDataAdapter(query, sqlConn);
            int i;


            DataSet ds = new DataSet();
            DataTable dt_gap = new DataTable();
            try
            {
                sqlConn.Open();


                ct.CommandText = query;
                sqla.SelectCommand = ct;
                sqla.Fill(ds);

            }
            catch (SqlException ee)
            {
                Console.WriteLine(query);
                Console.WriteLine(ee.ToString());
                return null;
            }
            finally
            {
                sqlConn.Close();

            }
            dt_gap = ds.Tables[0];

            DataRowCollection gaprc = dt_gap.Rows;
            DataRow dr;

            dr = gaprc[0];

            string[] res = new string[gaprc.Count];
            for (i = 0; i < gaprc.Count; i++)
            {
                dr = gaprc[i];
                res[i] = "";

                foreach (object item in dr.ItemArray)
                {
                    res[i] += " "+item.ToString();
                }
            }

            return res;
        }


        public String[] GetResult(string query, Points ps)
        {
            SqlConnection sqlConn = new SqlConnection("Data Source=147.46.240.145;Initial Catalog=Next;User ID = Next;Password=GAopt3132;Timeout=10000");
            SqlCommand ct = sqlConn.CreateCommand(); //DDL
            ct.CommandTimeout = 100000;
            SqlDataAdapter sqla = new SqlDataAdapter(query, sqlConn);
            int i;


            DataSet ds = new DataSet();
            DataTable dt_gap = new DataTable();
            try
            {
                sqlConn.Open();


                ct.CommandText = query;
                sqla.SelectCommand = ct;
                sqla.Fill(ds);

            }
            catch (SqlException ee)
            {
                Console.WriteLine(query);
                Console.WriteLine(ee.ToString());
                return null;
            }
            finally
            {
                sqlConn.Close();

            }
            dt_gap = ds.Tables[0];

            DataRowCollection gaprc = dt_gap.Rows;
            DataRow dr;

            for (i = 0; i < gaprc.Count; i++)
            {
                dr = gaprc[i];
                Pot pot = new Pot();
                pot.Time = DateTime.Parse(dr[0].ToString());
                if (dr[1].ToString() != "")
                    pot.WIFI = (int)dr[1];
                if (dr[2].ToString() != "")
                    pot.Brightness = (int)dr[2];
                if (dr[3].ToString() != "")
                    pot.BT = (int)dr[3];
                if (dr[4].ToString() != "")
                    pot.Sound = (int)dr[4];
                if (dr[5].ToString() != "")
                    pot.GPS = (int)dr[5];
                if (dr[6].ToString() != "")
                    pot.Lat = double.Parse(dr[6].ToString());
                if (dr[7].ToString() != "")
                    pot.Lon = double.Parse(dr[7].ToString());
                if (dr[9].ToString() != "")
                    pot.App= dr[9].ToString();
                if (dr[10].ToString() != "")
                    pot.headset = (int)dr[10];
                if (dr[11].ToString() != "")
                    pot.plugged = (int)dr[11];
                if (dr[12].ToString() != "")
                    pot.Accuracy = float.Parse(dr[12].ToString());
                if (dr[13].ToString() != "")
                    pot.Speed = float.Parse(dr[13].ToString());
                ps.mPt.Add(pot);
            }

            return null;
        }
        private string sql;
        public Object[] data;
        private int dataIndex;
        private SqlConnection sqlConn;
        private DataRowCollection drCol;
        private static DBMS mDBMS;
    }
}
